﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CoreFrame.Business;
using System.Data;
using MarchEstate.Manager.BusinesEntity;
using CoreFrame.Core.Orm;

namespace MarchEstate.Manager.Dao.Cash
{
    public class CashManagerDao : AbstractDao
    {
        public DataTable GetRoomData(string areacode, string buildcode, int usestatus)
        {
            string strsql = @" SELECT     b.RoomId as srccId,  b.RoomNumber as srccName,  c.OwnerId, c.Name as OwnerName, b.FloorNum, b.UseArea, c.Sex, c.OftenMobile,c.CardNum,1 type,a.JoinFlag
                                FROM  WY_Room AS b LEFT OUTER JOIN
                                      WY_RoomOwnerRelation AS a ON a.RoomId = b.RoomId LEFT OUTER JOIN
                                      WY_Owner AS c ON a.OwnerId = c.OwnerId
                                WHERE     (b.DeleteFlag = 0) AND (b.UseType in (1,2)) AND (b.AreaCode = '{0}') AND (b.BuildingCode = '{1}') AND (b.UseStatus = {2})
                                order by b.FloorNum,b.RoomNumber";
            strsql = string.Format(strsql, areacode, buildcode, usestatus);
            return oleDb.GetDataTable(strsql);
        }

        public DataTable GetShopData()
        {
            string strsql = @"SELECT     a.ShopId as srccId, a.ShopName as srccName,a.OwnerId, a.OwnerName, 1 AS FloorNum, a.UseArea, -1 Sex, b.OftenMobile,b.CardNum, 2 AS type,a.JoinFlag
                                FROM  WY_Shop AS a LEFT OUTER JOIN WY_Owner b on a.ownerId=b.ownerid
                                WHERE     (a.DeleteFlag = 0)";
            //strsql = string.Format(strsql, areacode, buildcode, usestatus);
            return oleDb.GetDataTable(strsql);
        }

        public DataTable GetCarportData()
        {
            string strsql = @"SELECT     CarPortId AS srccId, a.Code AS srccName,a.OwnerId, a.OwnerName, a.CarAreaCode AS FloorNum, a.Area AS UseArea,  -1 Sex, b.OftenMobile,b.CardNum, 3 AS type,a.JoinFlag
                                FROM  WY_Carport AS a LEFT OUTER JOIN WY_Owner AS b ON a.OwnerId = b.OwnerId";
            return oleDb.GetDataTable(strsql);
        }

        public DataTable GetClienteleData()
        {
            string strsql = @"SELECT     a.CustomerId AS srccId, a.CustomerName AS srccName,a.OwnerId, a.OwnerName, 1 AS FloorNum, '0' AS UseArea, -1 Sex, b.OftenMobile,b.CardNum, 4 AS type,a.JoinFlag
                                FROM   WY_Customer AS a LEFT OUTER JOIN WY_Owner AS b ON a.OwnerId = b.OwnerId
	                            where a.DeleteFlag=0";
            return oleDb.GetDataTable(strsql);
        }

        public DataRow GetSrccInfo(string srccId)
        {
            string strsql = @"select A.OwnerId,B.Name as OwnerName,srccName,A.UseArea from (
                                select c.OwnerId,b.RoomId as srccId,b.RoomNumber as srccName,b.UseArea from 
								WY_Room b LEFT OUTER JOIN 
								WY_RoomOwnerRelation AS a ON a.RoomId = b.RoomId 
								LEFT OUTER JOIN
								WY_Owner AS c ON a.OwnerId = c.OwnerId  
                                union all
                                select OwnerId,ShopId as srccId, ShopName as srccName,UseArea from WY_Shop
                                union all
                                select OwnerId,CarPortId AS srccId, Code AS srccName,1 UseArea from WY_Carport
                                union all
                                select OwnerId,CustomerId AS srccId, CustomerName AS srccName, 1 UseArea from WY_Customer
                                ) A  LEFT OUTER JOIN WY_Owner B ON A.OwnerId = B.OwnerId
                                where A.srccId='" + srccId + "'";
            return oleDb.GetDataRow(strsql);
        }

        public bool AddItemFeeOrder(AbstractItemFeeOrder feeOrder)
        {
            BindEntity<AbstractItemFeeOrder>.CreateInstanceDAL(oleDb, WorkId).Add(feeOrder);
            return true;
        }


        public bool UpdateThisFee(string _srccId, string _itemcode, decimal _thisCostFee, decimal _thisLateFee, decimal _FavourFee, decimal _RealityFee, decimal _PrepayFee)
        {
            string strsql = @"update WY_OSRCCItemRelation set ThisCostFee={2},ThisLateFee={3},FavourFee={4},RealityFee={5},PrepayFee={6} where SrccId='{0}' and ItemCode='{1}'";
            strsql = string.Format(strsql, _srccId,_itemcode, _thisCostFee, _thisLateFee,_FavourFee,_RealityFee,_PrepayFee);
            return oleDb.DoCommand(strsql) > 0;
        }

        public bool UpdateThisFee(string _srccId, string _itemcode, DateTime _Thismaturity, decimal _thisCostFee, decimal _thisLateFee, decimal _FavourFee, decimal _RealityFee, decimal _PrepayFee)
        {
            string strsql = @"update WY_OSRCCItemRelation set Thismaturity='{2}',ThisCostFee={3},ThisLateFee={4},FavourFee={5},RealityFee={6},PrepayFee={7} where SrccId='{0}' and ItemCode='{1}'";
            strsql = string.Format(strsql, _srccId, _itemcode,_Thismaturity, _thisCostFee, _thisLateFee, _FavourFee, _RealityFee, _PrepayFee);
            return oleDb.DoCommand(strsql) > 0;
        }

        public bool UpdateThisFee(string _srccId, string _itemcode, DateTime _Lastmaturity, DateTime _Thismaturity, decimal _Lastsurplus, decimal _LastLateFee)
        {
            string strsql = @"update WY_OSRCCItemRelation set Lastmaturity='{0}',Thismaturity='{1}', Lastsurplus={2},LastLateFee={3},InitFlag=1 where SrccId='{4}' and ItemCode='{5}'";
            strsql = string.Format(strsql,_Lastmaturity,_Thismaturity,_Lastsurplus,_LastLateFee,_srccId,_itemcode);
            return oleDb.DoCommand(strsql) > 0;
        }

        public bool UpdateThisFee(string _srccId, string _itemcode, DateTime _Lastmaturity, DateTime _Thismaturity, decimal _Lastsurplus, decimal _LastLateFee, decimal _thisCostFee, decimal _thisLateFee, decimal _FavourFee, decimal _RealityFee, decimal _PrepayFee)
        {
            string strsql = @"update WY_OSRCCItemRelation set Lastmaturity='{2}',Thismaturity='{3}', Lastsurplus={4},LastLateFee={5}, ThisCostFee={6},ThisLateFee={7},FavourFee={8},RealityFee={9},PrepayFee={10} where SrccId='{0}' and ItemCode='{1}'";
            strsql = string.Format(strsql, _srccId, _itemcode,_Lastmaturity,_Thismaturity,_Lastsurplus,_LastLateFee, _thisCostFee, _thisLateFee, _FavourFee, _RealityFee, _PrepayFee);
            return oleDb.DoCommand(strsql) > 0;
        }

        public List<AbstractOSRCCItemRelation> GetSrccItemList(string _srccId)
        {
            string strWhere = @"(SrccId = '" + _srccId + "')";
            return BindEntity<AbstractOSRCCItemRelation>.CreateInstanceDAL(oleDb, WorkId).GetListArray(strWhere);
        }

        public AbstractOSRCCItemRelation GetSrccItem(string _srccId, string _itemcode)
        {
            string strWhere = "SrccId='" + _srccId + "' and ItemCode='"+_itemcode+"'";
            return BindEntity<AbstractOSRCCItemRelation>.CreateInstanceDAL(oleDb, WorkId).GetModel(strWhere);
        }
        
        public DataTable GetSrccitemData(string _srccId)
        {
            //新增其他类型的项目 不在表WY_OSRCCItemRelation中，用UNION ALL加载出来
            string strsql = @"SELECT   NULL AS HeadId, a.OwnerId, a.SrccId, a.ItemCode, a.ItemName, a.Lastmaturity, a.Thismaturity, a.Lastsurplus, a.LastLateFee, a.ThisCostFee, a.ThisLateFee, a.WorkId, 
                      b.Price, 0 AS ThisNum,
                      a.FavourFee,
                      a.RealityFee,
                      a.PrepayFee,
                    b.ItemType,'' as SrccItemMemo
                      FROM         WY_OSRCCItemRelation AS a LEFT OUTER JOIN
                      WY_ServiceItem AS b ON a.ItemCode = b.Code where a.srccid='" + _srccId+@"'
                        UNION ALL
                          SELECT     a.HeadId, a.OwnerId, a.SrccId, a.ItemCode, a.ItemName, a.Lastmaturity, a.Thismaturity, 0 AS Lastsurplus, 0 AS LastLateFee, 
                                                  a.CostFee ThisCostFee, a.LateFee ThisLateFee, a.WorkId, a.Price, a.DosageNum AS ThisNum, 0 AS FavourFee, a.CostFee AS RealityFee, 
                                                  0 AS PrepayFee, b.ItemType, a.SrccItemMemo
                           FROM         WY_ItemFeeHead AS a LEFT JOIN
                                                  WY_ServiceItem AS b ON a.ItemCode = b.Code
                           WHERE   (select count(*) from WY_OSRCCItemRelation where srccid='"+_srccId+"' and itemcode=a.itemcode)=0 AND  (a.SrccId = '" + _srccId + @"') AND (ChargeID IS NULL);";

            return oleDb.GetDataTable(strsql);

        }

        public DataTable GetSrccitemData()
        {
            //过滤掉未入伙的Srcc
            string strsql = @"select * from (
                                SELECT     a.SrccId, a.ItemCode,b.ItemName, b.StatCode, a.Thismaturity,b.Price
                                                                FROM         WY_OSRCCItemRelation AS a LEFT OUTER JOIN
                                                                                      WY_ServiceItem AS b ON a.ItemCode = b.Code
                                                                WHERE     (b.ItemType = 0) 
                                ) B where (select JoinFlag from(
                                select RoomId as srccId, JoinFlag from 
		                                WY_RoomOwnerRelation 
                                        union all
                                        select ShopId as srccId,JoinFlag from WY_Shop where DeleteFlag=0
                                        union all
                                        select CarPortId as srccId,JoinFlag from WY_Carport
                                        union all
                                        select CustomerId as srccId,JoinFlag from WY_Customer where DeleteFlag=0
                                ) A where srccId=B.SrccId)=1";
            return oleDb.GetDataTable(strsql);
        }

        public bool DeleteSrccItem(string _srccId, string _itemcode)
        {
            string strsql = @"delete from WY_OSRCCItemRelation where SrccId='{0}' and ItemCode='{1}'";
            strsql = string.Format(strsql, _srccId, _itemcode);
            return oleDb.DoCommand(strsql) > 0;
        }

        public bool DeleteSrccItem(string _srccId)
        {
            string strsql = @"delete from WY_OSRCCItemRelation where SrccId='{0}'";
            strsql = string.Format(strsql, _srccId);
            return oleDb.DoCommand(strsql) > 0;
        }


        public DataTable GetitemHeadData(string _headId, string _srccId, string _itemcode)
        {
            string strwhereheadid = "";
            if (_headId != "")
            {
                strwhereheadid = " And HeadId='" + _headId + "'";
            }
            string strsql = @"SELECT     HeadId, OwnerId, OwnerName, SrccId, SrccSonId, ItemCode, ItemName, Lastmaturity, Thismaturity, DosageNum, Price, CostFee, LateFee, ChargeID
                                FROM         WY_ItemFeeHead
                                WHERE     (SrccId = '{0}') AND (ItemCode = '{1}') AND (ChargeID IS NULL) {2}";

            strsql = string.Format(strsql, _srccId, _itemcode, strwhereheadid);
            return oleDb.GetDataTable(strsql);
        }

        public bool DeleteItemFeeHead(string _headId)
        {
            string strsql = @"delete from wy_itemfeehead where headid='"+_headId+"'";
            return oleDb.DoCommand(strsql) > 0;
        }

        public bool DeleteItemFeeOrder(string _headId)
        {
            string strsql = @"delete from wy_itemfeeorder where headid='" + _headId + "'";
            return oleDb.DoCommand(strsql) > 0;
        }

        public List<AbstractItemFeeHead> GetItemFeeHead(string _srccId, string _itemcode)
        {
            string strWhere = "SrccId='" + _srccId + "' and ItemCode='" + _itemcode + "' AND (ChargeID IS NULL)";
            return BindEntity<AbstractItemFeeHead>.CreateInstanceDAL(oleDb, WorkId).GetListArray(strWhere);
        }


        public bool ItemFeeHeadUpdateChargeId(string headId, string chargeId)
        {
            string strsql = @"update WY_ItemFeeHead set ChargeID='"+chargeId+"' where headId='"+headId+"'";
            return oleDb.DoCommand(strsql) > 0;
        }

        public bool ItemFeeHeadCancelChargeId(string chargeId)
        {
            string strsql = @"update WY_ItemFeeHead set ChargeID=NULL where ChargeID='" + chargeId + "'";
            return oleDb.DoCommand(strsql) > 0;
        }

        public List<AbstractChargeMoney> GetChargeMoneyList(string _srccId,PageInfo page)
        {
            string strWhere = "SrccId='" + _srccId + "'";
            page.KeyName = "ChargeDate|desc";
            return BindEntity<AbstractChargeMoney>.CreateInstanceDAL(oleDb, WorkId).GetListArray(strWhere,page);
        }

        public List<AbstractChargeMoneyDetail> GetChargeDetailList(string chargeId)
        {
            string strWhere = "ChargeID='" + chargeId + "'";
            return BindEntity<AbstractChargeMoneyDetail>.CreateInstanceDAL(oleDb, WorkId).GetListArray(strWhere);
        }

        public string GetLastChargeId()
        {
            string strsql = @"select top 1 ChargeID from WY_ChargeMoney where ChargeFlag=0 order by ChargeDate desc";
            return oleDb.GetDataResult(strsql).ToString();
        }

        public bool AlterChargeFlag(string chargeId,int flag)
        {
            string strsql = @"update WY_ChargeMoney set ChargeFlag=" + flag + " where ChargeID='" + chargeId + "'";
            return oleDb.DoCommand(strsql) > 0;
        }
        /// <summary>
        /// 删除未收银的
        /// </summary>
        /// <param name="_srccId"></param>
        /// <param name="_itemcode"></param>
        /// <returns></returns>
        public bool DeleteItemFeeHeadAndOrder(string _srccId, string _itemcode)
        {
            string strsql = @"delete from WY_ItemFeeOrder where HeadId in (select HeadId from WY_ItemFeeHead where SrccId='{0}' and ItemCode='{1}' AND (ChargeID IS NULL))";
            strsql = string.Format(strsql, _srccId, _itemcode);
            oleDb.DoCommand(strsql);

            strsql = @"delete from WY_ItemFeeHead where SrccId='{0}' and ItemCode='{1}' AND (ChargeID IS NULL)";
            strsql = string.Format(strsql, _srccId, _itemcode);
            oleDb.DoCommand(strsql);
            return true;
        }

        public int GetJoinRoomNum(DateTime joindate)
        {
            string strsql = @"select count(*) from WY_RoomOwnerRelation where JoinDate='"+joindate.Date+"'";
            return Convert.ToInt32(oleDb.GetDataResult(strsql));
        }

        public int GetJoinCarportNum(DateTime joindate)
        {
            string strsql = @"select count(*) from WY_Carport where JoinDate='"+joindate.Date+"'";
            return Convert.ToInt32(oleDb.GetDataResult(strsql));
        }

        public List<AbstractChargeMoney> GetAccountChargeList(string accountId)
        {
            string strWhere;
            if (accountId == null)
                strWhere = "AccountId is NULL";
            else
                strWhere = "AccountId='" + accountId + "'";
            return BindEntity<AbstractChargeMoney>.CreateInstanceDAL(oleDb, WorkId).GetListArray(strWhere);
        }

        public System.Data.DataTable GetAccountItemClassData(string accountId)
        {
            string strWhere;
            if (accountId == null)
                strWhere = "AccountId is NULL";
            else
                strWhere = "AccountId='" + accountId + "'";
            string strsql = @"SELECT     b.ItemCode, b.ItemName, b.StatCode, SUM(b.RealityFee) AS RealityFee, SUM(CASE WHEN b.RealityFee < 0 THEN b.RealityFee ELSE 0 END) 
                              AS backfee, SUM(b.FavourFee) AS favourfee
                                FROM         WY_ChargeMoney AS a LEFT OUTER JOIN
                                             WY_ChargeMoneyDetail AS b ON a.ChargeID = b.ChargeID
                                Where " + strWhere + @"
                                GROUP BY b.ItemCode, b.ItemName, b.StatCode";
            return oleDb.GetDataTable(strsql);
        }

        public AbstractAccount GetAccount(string accountId)
        {
            string strWhere="AccountId='" + accountId + "'";
            return BindEntity<AbstractAccount>.CreateInstanceDAL(oleDb, WorkId).GetModel(strWhere);
        }

        public List<AbstractAccount> GetAccountList(DateTime bdate, DateTime edate)
        {
            string strWhere = "AccountDate>='" + bdate + "' and AccountDate<='" + edate + "'";
            return BindEntity<AbstractAccount>.CreateInstanceDAL(oleDb, WorkId).GetListArray(strWhere);
        }

        public bool ChargeMoneyAlterAccountId(string accountId)
        {
            string strsql = "update WY_ChargeMoney set AccountID='" + accountId + "' where AccountID is NULL";
            return oleDb.DoCommand(strsql) > 0;
        }

        public decimal GetTotalFee(string accountId)
        {
            string strsql = @"select sum(RealityFee) from WY_ChargeMoney where AccountID is NULL";
            object value = oleDb.GetDataResult(strsql);
            return Convert.ToDecimal(value == DBNull.Value ? 0 : value);
        }


        public DataTable SearchRoom(string areacode, string buildcode, string roomnumber,PageInfo page)
        {
            string strsql = @" SELECT     b.RoomId,  b.RoomNumber,c.OwnerId, c.Name as OwnerName, b.UseArea, c.OftenMobile,c.CardNum
                                FROM  WY_Room AS b LEFT OUTER JOIN
                                      WY_RoomOwnerRelation AS a ON a.RoomId = b.RoomId LEFT OUTER JOIN
                                      WY_Owner AS c ON a.OwnerId = c.OwnerId
                                WHERE (b.DeleteFlag = 0) AND (b.UseType in (1,2)) AND (b.AreaCode = '{0}') AND (b.BuildingCode = '{1}') AND (b.UseStatus = 1) AND (b.RoomNumber like '%{2}%')
                                ";
            strsql = string.Format(strsql, areacode, buildcode, roomnumber);
            page.KeyName="RoomNumber";
            strsql = CoreFrame.Common.FilterString.FormatSql(strsql, page, oleDb);
            return oleDb.GetDataTable(strsql);
        }

        public DataTable SearchOwner(string ownername, string tel, string number,PageInfo page)
        {
            string strsql = @"select OwnerId,Name,Sex,OftenMobile,CardNum from WY_Owner
                              where DeleteFlag=0 and ((Name like '%{0}%') or (OftenMobile='{1}') or (CardNum='{2}'))";

            strsql = string.Format(strsql, ownername, tel, number);
            page.KeyName = "Name";
            strsql = CoreFrame.Common.FilterString.FormatSql(strsql, page, oleDb);
            return oleDb.GetDataTable(strsql);
        }
    }
}
